﻿Imports System.Data.SqlClient
Imports System.Data
Public Class TaiKhoanDAO
    Public Function GetItemID(ByVal tenDN As String) As TaiKhoanDTO
        Dim tk As New TaiKhoanDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from TAIKHOAN"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            tk.MaTaiKhoan = Convert.ToInt32(data.Rows(data.Rows.Count - 1)("MaTaiKhoan").ToString())
            tk.TenTaiKhoan = data.Rows(data.Rows.Count - 1)("TenTaiKhoan").ToString()
            tk.MatKhau = data.Rows(data.Rows.Count - 1)("MatKhau").ToString()
            tk.MaLoai = Convert.ToInt32(data.Rows(data.Rows.Count - 1)("MaLoai").ToString())
            tk.TrangThai = Convert.ToInt32(data.Rows(data.Rows.Count - 1)("TrangThai").ToString())
        Catch ex As Exception

        End Try

        Return tk
    End Function
    Public Function GetAll() As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from TAIKHOAN"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function GetItemLoaiTK(ByVal loaiTK As Integer) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from TAIKHOAN where MaLoai= " + loaiTK.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function GetItemTrangThai(ByVal trangthai As Integer) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from TAIKHOAN where TrangThai= " + trangthai.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function Insert(ByVal tk As TaiKhoanDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_InsertTAIKHOAN @TenTaiKhoan,@MatKhau,@MaLoai,@TrangThai,@MaTaiKhoan"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = 1
            para = cmd.Parameters.Add("@TenTaiKhoan", SqlDbType.VarChar)
            para.Value = tk.TenTaiKhoan
            para = cmd.Parameters.Add("@MatKhau", SqlDbType.VarChar)
            para.Value = tk.MatKhau
            para = cmd.Parameters.Add("@MaLoai", SqlDbType.Int)
            para.Value = tk.MaLoai
            para = cmd.Parameters.Add("@TrangThai", SqlDbType.Int)
            para.Value = tk.TrangThai
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function
    Public Sub Delete(ByVal tk As TaiKhoanDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_DeleteTAIKHOAN @MaTaiKhoan"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = tk.MaTaiKhoan
            cmd.ExecuteNonQuery()
            MessageBox.Show("Tài khoản đã được xóa thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Function CheckDangNhap(ByVal tk As TaiKhoanDTO) As Integer
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "usp_KTDangNhap"
            cmd.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter = cmd.Parameters.Add("@TenTaiKhoan", SqlDbType.VarChar)
            para.Value = tk.TenTaiKhoan

            para = cmd.Parameters.Add("@MatKhau", SqlDbType.VarChar)
            para.Value = tk.MatKhau

            Dim a As SqlParameter = New SqlParameter("@Flag", SqlDbType.Int)
            a.Direction = ParameterDirection.Output
            cmd.Parameters.Add(a)

            cmd.ExecuteScalar()

            Dim flag As Integer = cmd.Parameters("@Flag").Value

            If flag = 0 Then
                MessageBox.Show("Thông tin đăng nhập sai", "Thông báo")
                Return 0
            Else
                Return flag
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return 0
        End Try
    End Function
    Public Function CheckID(ByVal tk As TaiKhoanDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "usp_KTTonTaiTAIKHOAN"
            cmd.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter = cmd.Parameters.Add("@TenTaiKhoan", SqlDbType.VarChar)
            para.Value = tk.TenTaiKhoan

            Dim a As SqlParameter = New SqlParameter("@flag", SqlDbType.Int)
            a.Direction = ParameterDirection.Output
            cmd.Parameters.Add(a)

            cmd.ExecuteScalar()

            Dim flag As Integer = cmd.Parameters("@flag").Value
            If flag = 1 Then
                MessageBox.Show("Tai khoan da ton tai", "Thông báo")
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return True
        End Try
        Return False
    End Function
    Public Sub Update(ByVal tk As TaiKhoanDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateTAIKHOAN @MaTaiKhoan,@TenTaiKhoan,@MatKhau,@MaLoai,@TrangThai"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = tk.MaTaiKhoan
            para = cmd.Parameters.Add("@TenTaiKhoan", SqlDbType.VarChar)
            para.Value = tk.TenTaiKhoan
            para = cmd.Parameters.Add("@MatKhau", SqlDbType.VarChar)
            para.Value = tk.MatKhau
            para = cmd.Parameters.Add("@MaLoai", SqlDbType.Int)
            para.Value = tk.MaLoai
            para = cmd.Parameters.Add("@TrangThai", SqlDbType.Int)
            para.Value = tk.TrangThai
            cmd.ExecuteNonQuery()
            MessageBox.Show("Tài khoản đã được cập nhật thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub UpdateTrangThai(ByVal tk As TaiKhoanDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateTrangThaiTAIKHOAN @MaTaiKhoan,@TrangThai"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = tk.MaTaiKhoan
            para = cmd.Parameters.Add("@TrangThai", SqlDbType.Int)
            para.Value = tk.TrangThai
            cmd.ExecuteNonQuery()
            'MessageBox.Show("Tài khoản đã được cập nhật thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class
